﻿if object_id('fn_MapHTML_AssetDistributionPole') is not null
DROP FUNCTION fn_MapHTML_AssetDistributionPole

GO

CREATE FUNCTION fn_MapHTML_AssetDistributionPole
(
	@PoleId int
)
RETURNS varchar(max)
AS
BEGIN
declare @html varchar(max)
set @html = 
(
	select
		 'Distribution Pole' as 'TITLE'
		,Poles.*
		,maps.Name as 'MapName'
		,sections.Name as 'SectionName'
		,species.Name as 'SpecieName'
		,classes.Name as 'ClassName'
		
		-- Pole images
		,(select
			 replace(replace(replace(replace(Src.SettingValue, '{0}', Images.Utility_Asset_Distributions_ImageId), '{1}', 'AssetDistribution'), '{2}', 85), '{3}', 85) as '@src'
			,replace(replace(replace(replace(Href.SettingValue, '{0}', Images.Utility_Asset_Distributions_ImageId), '{1}', 'AssetDistribution'), '{2}', 640), '{3}', 480) as '@href'
			,poles.Utility_Asset_DistributionId as PoleId
			,images.Utility_Asset_Distributions_ImageId as ImageId
		from
			tb_Utility_Asset_Distributions poles
			join tb_Utility_Asset_Distributions_Images images
				on images.Utility_Asset_DistributionFK = poles.Utility_Asset_DistributionId 
		for xml path ('IMAGE'), type) as '*'

		-- distribution pole visits <POLE><TITLE /></POLE>
		,(select 
				 'GPS Visit' as 'TITLE'
				,SubVisit.*
				,maps.Name as 'MapName'
				,sections.Name as 'SectionName'
				,species.Name as 'SpecieName'
				,classes.Name as 'ClassName'
				,lengths.Name as 'LengthName'
				,treatments.Name as 'TreatMentName'

				-- visit images <IMAGE href="" src="" />

				,(select
					 replace(replace(replace(replace(Src.SettingValue, '{0}', Images.tb_Vendor_Distribution_Images_ImageId), '{1}', 'GPSPoleDistribution'), '{2}', 85), '{3}', 85) as '@src'
					,replace(replace(replace(replace(Href.SettingValue, '{0}', Images.tb_Vendor_Distribution_Images_ImageId), '{1}', 'GPSPoleDistribution'), '{2}', 480), '{3}', 640) as '@href'
					,Images.*
				from 
					tb_Vendor_Distribution_Images as Images
				where 
					Images.Vendor_Distribution_PoleFk = SubVisit.Vendor_Distribution_Poles_PoleId
				for xml path ('IMAGE'), type) as '*'

			from 
				tb_Vendor_Distribution_Poles as SubVisit
				LEFT JOIN tb_Utility_Polygons maps
					ON SubVisit.MapFk = maps.Utility_PolygonId 
				LEFT JOIN tb_Utility_Polygons sections
					ON SubVisit.SectionFk = sections.Utility_PolygonId 
				LEFT JOIN tb_XMissionStructures_PoleSpecies species
					ON SubVisit.SpecieFk = species.XMissionStructures_PoleSpeciesId 
				LEFT JOIN tb_Distribution_Classes classes
					ON SubVisit.ClassFk = classes.ClassId 
				LEFT JOIN tb_Distribution_Lengths lengths
					ON SubVisit.LengthFk = lengths.LengthId
				LEFT JOIN tb_XMissionStructures_PoleTreatments treatments
					ON SubVisit.TreatmentFk = treatments.XMissionStructures_PoleTreatmentId 				
			where
				SubVisit.Utility_Asset_Distributions_PoleFk = Poles.Utility_Asset_DistributionId
				and SubVisit.DistributionType = 0
			for xml path ('VISIT'), type)

		,(select 
				 'Inspection Visit' as 'TITLE'
				,SubVisit.*
				,maps.Name as 'MapName'
				,sections.Name as 'SectionName'
				,species.Name as 'SpecieName'
				,classes.Name as 'ClassName'
				,lengths.Name as 'LengthName'
				,treatments.Name as 'TreatMentName'

				-- visit images <IMAGE href="" src="" />

				,(select
					 replace(replace(replace(replace(Src.SettingValue, '{0}', Images.tb_Vendor_Distribution_Images_ImageId), '{1}', 'InspectionPoleDistribution'), '{2}', 85), '{3}', 85) '@src'
					,replace(replace(replace(replace(Href.SettingValue, '{0}', Images.tb_Vendor_Distribution_Images_ImageId), '{1}', 'InspectionPoleDistribution'), '{2}', 640), '{3}', 480) as '@href'
					,Images.*
				from 
					tb_Vendor_Distribution_Images as Images
				where 
					Images.Vendor_Distribution_PoleFk = SubVisit.Vendor_Distribution_Poles_PoleId
				for xml path ('IMAGE'), type) as '*'

			from 
				tb_Vendor_Distribution_Poles as SubVisit
				LEFT JOIN tb_Utility_Polygons maps
					ON SubVisit.MapFk = maps.Utility_PolygonId 
				LEFT JOIN tb_Utility_Polygons sections
					ON SubVisit.SectionFk = sections.Utility_PolygonId 
				LEFT JOIN tb_XMissionStructures_PoleSpecies species
					ON SubVisit.SpecieFk = species.XMissionStructures_PoleSpeciesId 
				LEFT JOIN tb_Distribution_Classes classes
					ON SubVisit.ClassFk = classes.ClassId 
				LEFT JOIN tb_Distribution_Lengths lengths
					ON SubVisit.LengthFk = lengths.LengthId
				LEFT JOIN tb_XMissionStructures_PoleTreatments treatments
					ON SubVisit.TreatmentFk = treatments.XMissionStructures_PoleTreatmentId 	
			where
				SubVisit.Utility_Asset_Distributions_PoleFk = Poles.Utility_Asset_DistributionId
				and SubVisit.DistributionType = 1
			for xml path ('VISIT'), type)

	from
		tb_Utility_Asset_Distributions as Poles
		left join tb_Settings as Href on Href.SettingId = 25
		left join tb_Settings as Src on Src.SettingId = 24
		left join tb_Utility_Polygons maps on poles.MapFk = maps.Utility_PolygonId 
		left join tb_Utility_Polygons sections on poles.SectionFk = sections.Utility_PolygonId
		left join tb_XMissionStructures_PoleSpecies species on poles.SpeciesFk = species.XMissionStructures_PoleSpeciesId
		left join tb_XMissionStructures_PoleClasses classes on poles.ClassFk = classes.XMissionStructures_PoleClassId
	where
		Poles.Utility_Asset_DistributionId = @PoleId
	for xml path ('Pole')

	)
	return @html

END
GO

select convert(xml, dbo.fn_MapHTML_AssetDistributionPole(151))


